
#Load the Lithnet FIM PowerShell Module
Import-Module LithnetRMA

#Connect to the FIM service instance
Set-ResourceManagementClient -BaseAddress http://localhost:5725

#Declare some variables
$ArcReqTime=""

#read the requests
$GetRequests = Search-Resources -XPath "/Request[((starts-with(DisplayName,'Update to Person')) or (starts-with(DisplayName,'Create Person'))) and CreatedTime >= op:subtract-dayTimeDuration-from-dateTime(fn:current-dateTime(), xs:dayTimeDuration('P31D'))]" -AttributesToGet @("TargetObjectType";"DisplayName";"RequestStatus";"CreatedTime";"Creator";"Target";"RequestParameter")
If ($GetRequests -ne $null){
ForEach ($GetRequest in $GetRequests){
$WriteRequestDN=$GetRequest.DisplayName -Replace "'",""
$RequestDN=$GetRequest.DisplayName
[int]$RequestRecCount=0
$TargetObjectType=$GetRequest.TargetObjectType
$RequestorID=Get-Resource -ObjectType Person -AttributeName ObjectID -AttributeValue $GetRequest.Creator
$RequestorDN=$RequestorID.DisplayName
#$TargetGuid=[GUID]$GetRequest.Target
#$TargetID=Get-Resource -ID $TargetGuid
$TargetID=Get-Resource -ObjectType $TargetObjectType -AttributeName ObjectID -AttributeValue $GetRequest.Target
$TargetDN=$TargetID.DisplayName

#get the created time
$RequestCreatedtime=$GetRequest.CreatedTime
$AttribUpdated=""
$AttribUpdatedValue=""
If ($RequestDN.StartsWith("Create Person")){
[String]$RequestCreatedTime=$GetRequest.CreatedTime
#Connect and write to ReportingDB
$SQLServer = 'tlkfimmgr'
$SQLDBName = 'ReportingDB'
$SqlQuery = "Insert into FIMRequestPersonReport values ('"+$RequestCreatedTime+"','"+$RequestRecCount +"','"+$WriteRequestDN+"','"+$AttribUpdated+"','"+$AttribUpdatedValue+"','"+$TargetDN+"','"+$RequestorDN+"','"+$GetRequest.RequestStatus+"')"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlConnection.Open()
$SqlCmd = $SqlConnection.CreateCommand()
$SqlCmd.CommandText = $SqlQuery
$Sqlcmd.ExecuteNonQuery()
$SqlConnection.Close()
}

If ($RequestDN.StartsWith("Update to Person")){
$ReadParameters=$GetRequest.RequestParameter
foreach($ReadParameter in $Readparameters){
$NumAttribCount=0
[xml]$Thexmlreader = $ReadParameter  -replace '<Value xsi:type="xsd:string">','<Value>' -replace '<Value xsi:type="q1:guid">','<Value>#guid'

$AttribUpdated = $Thexmlreader.RequestParameter.PropertyName
$AttribUpdatedValue=""
If ($ReadParameter -match "</Value>"){
[String]$AttribUpdatedValue = $Thexmlreader.RequestParameter.Value
If ($AttribUpdatedValue.startswith("#guid")){
[int]$AttribUpdatedValueLength=$AttribUpdatedValue.length-5

#write-host $AttribUpdatedValueLength
$AttribUpdatedValueID=$AttribUpdatedValue.substring(5,$AttribUpdatedValueLength)
#write-host $AttribUpdatedValueID
$MyGuid = [GUID]($AttribUpdatedValueID)
$AttribUpdatedValueMyID=Get-Resource -ID $MyGuid
$AttribUpdatedValueDN=$AttribUpdatedValueMyID.DisplayName
$AttribUpdatedValue=$AttribUpdatedValueDN
}
}
If ($RequestCreatedtime -ne $ArcReqTime){$RequestRecCount++}
#write-host $GetRequest.CreatedTime $RequestRecCount $GetRequest.DisplayName $AttribUpdated $AttribUpdatedValue $TargetDN $RequestorDN $GetRequest.RequestStatus
[String]$RequestCreatedTime=$GetRequest.CreatedTime
#Connect and write to ReportingDB
$SQLServer = 'tlkfimmgr'
$SQLDBName = 'ReportingDB'
$SqlQuery = "Insert into FIMRequestPersonReport values ('"+$RequestCreatedTime+"','"+$RequestRecCount +"','"+$WriteRequestDN+"','"+$AttribUpdated+"','"+$AttribUpdatedValue+"','"+$TargetDN+"','"+$RequestorDN+"','"+$GetRequest.RequestStatus+"')"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlConnection.Open()
$SqlCmd = $SqlConnection.CreateCommand()
$SqlCmd.CommandText = $SqlQuery
$Sqlcmd.ExecuteNonQuery()
$SqlConnection.Close()
}
}

$ArcReqTime=$RequestCreatedtime

}
}
